﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Automobile.service.Model;
using System.Data;
using Automobile.service.Common;
using System.Data.SqlClient;
namespace Automobile.service.DAL
{
    public class ShipmentsDAL
    {
        SQLHelper h = new SQLHelper();
        /// <summary>
        /// 根据规格查询商品
        /// </summary>
        /// <param name="Product_Modelspe"></param>
        /// <returns></returns>
        public List<Product> SearchProductByModelspe(string Product_Modelspe)
        {
            string sql = "select Product_Id,Product_Name,Product_Modelspe,Product_Bprid,Product_Price,Product_Barcode,Product_frim,Product_Unit_Id,Product_Remark,Product_Type_Name,Product.Product_Type_Id from Product join Product_Type on Product.Product_Type_Id=Product_Type.Product_Type_Id where Product_Modelspe like '%" + Product_Modelspe + "%'";
            List<Product> list = new List<Product>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Product p = new Product();
                p.Product_Id = Convert.ToInt32(item["Product_Id"]);
                p.Product_Name = item["Product_Name"].ToString();
                p.Product_Modelspe = item["Product_Modelspe"].ToString();
                p.Product_Price = Convert.ToDecimal(item["Product_Price"]);
                p.Product_Bprid = Convert.ToDecimal(item["Product_Bprid"]);
                p.Product_Barcode = item["Product_Barcode"].ToString();
                p.Product_frim = item["Product_frim"].ToString();
                p.Product_Unit_Id = item["Product_Unit_Id"].ToString();
                p.Product_Remark = item["Product_Remark"].ToString();
                p.Product_Type_Name = item["Product_Type_Name"].ToString();
                p.Product_Type_Id = Convert.ToInt32(item["Product_Type_Id"]);

                list.Add(p);
            }
            return list;
        }
        /// <summary>
        /// 仓库下拉框
        /// </summary>
        /// <returns></returns>
        public List<WareHose> GetWareHoseList()
        {
            string sql = "select WareHose_Id,WareHose_Name from WareHose";
            List<WareHose> list = new List<WareHose>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                WareHose w = new WareHose();
                w.WareHose_Id = Convert.ToInt32(item["WareHose_Id"]);
                w.WareHose_Name = item["WareHose_Name"].ToString();
                list.Add(w);
            }
            return list;
        }
        /// <summary>
        /// 经办人下拉框
        /// </summary>
        /// <returns></returns>
        public List<Employee> GetEmployeeList()
        {
            string sql = "select Employee_Id,Employee_Name from Employee";
            List<Employee> list = new List<Employee>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Employee e = new Employee();
                e.Employee_Id = Convert.ToInt32(item["Employee_Id"]);
                e.Employee_Name = item["Employee_Name"].ToString();
                list.Add(e);
            }
            return list;
        }
        /// <summary>
        /// 会员下拉框
        /// </summary>
        /// <returns></returns>
        public List<MemberCard> GetMemberCard()
        {
            string sql = "select MemberCard_Id,MemberCard_Name from MemberCard";
            List<MemberCard> list = new List<MemberCard>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                MemberCard m = new MemberCard();
                m.MemberCard_Id = Convert.ToInt32(item["MemberCard_Id"]);
                m.MemberCard_Name = item["MemberCard_Name"].ToString();
                list.Add(m);
            }
            return list;
        }
        /// <summary>
        /// 添加采购入库单信息
        /// </summary>
        /// <param name="P"></param>
        /// <param name="xml"></param>
        /// <returns></returns>
        public int ShipMentsAddDo(Shipments s, string xml)
        {
            string str = "Proc_ShipMentsAdd";
            SqlParameter[] par = new SqlParameter[] { 
           
                       new SqlParameter("@Shipments_blitems",s.Shipments_blitems),
                       new SqlParameter("@Employee_Id",s.Employee_Id),
                       new SqlParameter("@MemberCard_Id",s.MemberCard_Id),
                       new SqlParameter("@WareHose_Id",s.WareHose_Id),
                       new SqlParameter("@Shipments_Price",s.Shipments_Price),
                       new SqlParameter("@xml",xml),
           
           };
            return h.ExecNonquery_Proc(str, par);
        }
        /// <summary>
        /// 主表显示
        /// </summary>
        /// <returns></returns>
        public List<Shipments> ShowShipments()
        {
            string sql = "select Shipments_Id,Shipments_blitems,Shipments_data,Employee_Name,MemberCard_Name,WareHose_Name,Shipments_Price,Shipments_State from  Shipments s join Employee e on e.Employee_Id=s.Employee_Id join MemberCard m on s.MemberCard_Id=m.MemberCard_Id join WareHose w on w.WareHose_Id=s.WareHose_Id order by Shipments_data DESC";
            List<Shipments> list = new List<Shipments>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Shipments s = new Shipments();
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Shipments_blitems = item["Shipments_blitems"].ToString();
                s.Shipments_data = Convert.ToDateTime(item["Shipments_data"]);
                s.Employee_Name = item["Employee_Name"].ToString();
                s.MemberCard_Name = item["MemberCard_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Shipments_Price = Convert.ToDecimal(item["Shipments_Price"]);
                s.Shipments_State =Convert.ToInt32(item["Shipments_State"]);

                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 根据id获取订单的主表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<Shipments> ShowShipmentsByid(int id)
        {
            string sql = "select Shipments_Id,Shipments_blitems,Shipments_data,Employee_Name,MemberCard_Name,WareHose_Name,Shipments_Price,Shipments_State from  Shipments s join Employee e on e.Employee_Id=s.Employee_Id join MemberCard m on s.MemberCard_Id=m.MemberCard_Id join WareHose w on w.WareHose_Id=s.WareHose_Id where Shipments_Id=" + id + "";
            List<Shipments> list = new List<Shipments>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                Shipments s = new Shipments();
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Shipments_blitems = item["Shipments_blitems"].ToString();
                s.Shipments_data = Convert.ToDateTime(item["Shipments_data"]);
                s.Employee_Name = item["Employee_Name"].ToString();
                s.MemberCard_Name = item["MemberCard_Name"].ToString();
                s.WareHose_Name = item["WareHose_Name"].ToString();
                s.Shipments_Price = Convert.ToDecimal(item["Shipments_Price"]);
                s.Shipments_State = Convert.ToInt32(item["Shipments_State"]);

                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 根据id获取订单的子表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ShipmentMate> ShowShipmentsMateByid(int id)
        {
            string sql="select ShipmentMate_Id,Shipments_Id,Product_Name,ShipmentMate_Num,ShipmentMate_Price from ShipmentMate join Product on ShipmentMate.Product_Id=Product.Product_Id where Shipments_Id="+id+"";
            List<ShipmentMate> list = new List<ShipmentMate>();
            DataTable data = h.ExecDataTable(sql);
            foreach (DataRow item in data.Rows)
            {
                ShipmentMate s = new ShipmentMate();
                s.ShipmentMate_Id = Convert.ToInt32(item["ShipmentMate_Id"]);
                s.Shipments_Id = Convert.ToInt32(item["Shipments_Id"]);
                s.Product_Name = item["Product_Name"].ToString();
                s.ShipmentMate_Num = Convert.ToInt32(item["ShipmentMate_Num"]);
                s.ShipmentMate_Price = Convert.ToDecimal(item["ShipmentMate_Price"]);

                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 审核付款
        /// </summary>
        /// <param name="Shipments_Id"></param>
        /// <returns></returns>
        public int ShipmentPay(int Shipments_Id)
        {
            string procname = "Proc_ShipmentPay";
            SqlParameter[] par = new SqlParameter[]{
                new SqlParameter("@Shipments_Id",Shipments_Id)
            };
            return h.ExecNonquery_Proc(procname,par);
        }
        public int count()
        {
            string sql = "select count(*) from Shipments";
            return Convert.ToInt32(h.ExecScalar(sql));

        }
    }
}
